﻿CREATE view [dbo].[Manufacturers] as

select distinct (select top 1 Manufacturer from devicedom b where a.ManufacturerUrl=b.ManufacturerUrl) as Manufacturer,ManufacturerUrl,count(*) as SchemaCount,(cast (count(*) as float)/(select count(*) from devicedom where domtype='d')) as Percentage
from devicedom a
where Manufacturer is not null
and Manufacturer!=''
and ManufacturerUrl is not null
and ManufacturerUrl!=''
and domtype='d'
group by ManufacturerUrl

union

select 'unknown' as Manufacturer,'' as ManufacturerUrl,count(*) as SchemaCount,(cast (count(*) as float)/(select count(*) from devicedom where domtype='d')) as Percentage
from devicedom
where Manufacturer=''
and domtype='d'
group by Manufacturer
